package org.abettor.nookinfo.persist;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.abettor.nookinfo.AddressConstant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * 小说持久化管理工具类
 * @author shawn
 *
 */
public class NovelHelper
{
	private Context context;

	public NovelHelper(Context context)
	{
		this.context = context;
	}

	/**
	 * 获取上次阅读章节信息
	 * @param local 是否为本地记录
	 * @return 失败返回null
	 */
	public synchronized LastRead getLastRead(boolean local)
	{
		String sql = "SELECT n_id, c_category, c_novel FROM tbl_last_read WHERE n_local = ?";
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{(local ? 1 : 0) + ""});
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		LastRead last = new LastRead();
		last.id = cursor.getInt(0);
		last.category = cursor.getString(1);
		last.novel = cursor.getString(2);

		cursor.close();
		db.close();
		doh.close();
		return last;
	}

	public synchronized void setLastRead(LastRead lastRead)
	{
		String sql = "SELECT n_id, c_category, c_novel FROM tbl_last_read WHERE n_local = ?";
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{(lastRead.local ? 1 : 0) + ""});
		if(cursor == null)
		{
			return;
		}

		cursor.moveToFirst();
		boolean flag = cursor.isAfterLast();
		cursor.close();
		if(flag)
		{
			ContentValues cv = new ContentValues();
			cv.put("n_id", lastRead.id);
			cv.put("n_local", (lastRead.local ? 1 : 0));
			cv.put("c_category", lastRead.category);
			cv.put("c_novel", lastRead.novel);
			db.insert("tbl_last_read", null, cv);
		}
		else
		{
			ContentValues cv = new ContentValues();
			cv.put("n_id", lastRead.id);
			cv.put("c_category", lastRead.category);
			cv.put("c_novel", lastRead.novel);
			db.update("tbl_last_read", cv, "n_local = ?", new String[]{(lastRead.local ? 1 : 0) + ""});
		}

		db.close();
		doh.close();
	}

	/**
	 * 获取本地小说页数
	 * @param pageSize
	 */
	public synchronized int getLocalNovelPageCount(int pageSize)
	{
		String sql = "SELECT COUNT(*) FROM tbl_novel";
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if(cursor == null)
		{
			db.close();
			doh.close();
			return 0;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return 0;
		}

		int total = cursor.getInt(0);
		int totalPage = total / pageSize;
		if(total % pageSize > 0)
		{
			totalPage ++;
		}

		cursor.close();
		db.close();
		doh.close();
		return totalPage;
	}

	/**
	 * 获取本地小说列表
	 * @param pageSize
	 * @param pageNo
	 */
	public synchronized List<LocalNovel> getLocalNovelList(int pageSize, int pageNo)
	{
		int startIdx = (pageNo - 1) * pageSize;
		String limit = startIdx + ", " + pageSize;

		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel", new String[]{"n_id", "c_name"}, null, null, null, null, "c_name asc", limit);
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		List<LocalNovel> array = new ArrayList<LocalNovel>();
		cursor.moveToFirst();
		while(! cursor.isAfterLast())
		{
			LocalNovel novel = new LocalNovel();
			novel.id = cursor.getInt(0);
			novel.name = cursor.getString(1);
			array.add(novel);

			cursor.moveToNext();
		}

		cursor.close();
		db.close();
		doh.close();
		return array;
	}

	/**
	 * 根据id获取本地小说
	 * @param id
	 * @return 成功返回本地小说对象，失败或小说不存在返回null
	 */
	public synchronized LocalNovel getLocalNovel(int id)
	{
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel", new String[]{"n_id", "c_name"}, "n_id = ?", new String[]{id + ""}, null, null, null, "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		LocalNovel novel = new LocalNovel();
		novel.id = cursor.getInt(0);
		novel.name = cursor.getString(1);

		cursor.close();
		db.close();
		doh.close();
		return novel;
	}

	/**
	 * 保存或更新小说信息
	 * @param id
	 * @param name
	 */
	public synchronized void saveOrUpdateLocalNovel(int id, String name)
	{
		LocalNovel localNovel = getLocalNovel(id);
		if(localNovel != null)
		{
			if(! name.equals(localNovel.name))
			{
				//	重复单不相同的小说，更新该记录
				DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
				SQLiteDatabase db = doh.getWritableDatabase();
				ContentValues cv = new ContentValues();
				cv.put("c_name", name);
				db.update("tbl_novel", cv, "n_id = ?", new String[]{id + ""});
				db.close();
				doh.close();
			}
		}
		else
		{
			//	保存小说记录
			DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
			SQLiteDatabase db = doh.getWritableDatabase();
			ContentValues cv = new ContentValues();
			cv.put("n_id", id);
			cv.put("c_name", name);
			db.insert("tbl_novel", null, cv);
			db.close();
			doh.close();
		}
	}

	/**
	 * 获取本地小说章节页数
	 * @param novel
	 * @param pageSize
	 */
	public synchronized int getLocalChapterPageCount(int novel, int pageSize)
	{
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		String sql = "SELECT COUNT(*) FROM tbl_novel_chapter WHERE n_novel = ?";
		Cursor cursor = db.rawQuery(sql, new String[]{novel + ""});
		if(cursor == null)
		{
			db.close();
			doh.close();
			return 0;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return 0;
		}

		int count = cursor.getInt(0);
		int page = count / pageSize;
		if(count % pageSize > 0)
		{
			page ++;
		}

		cursor.close();
		db.close();
		doh.close();
		return page;
	}

	/**
	 * 获取本地小说章节列表
	 * @param novel
	 * @param pageSize
	 * @param pageNo
	 */
	public synchronized List<LocalChapter> getLocalChapterList(int novel, int pageSize, int pageNo)
	{
		int startIdx = (pageNo - 1) * pageSize;
		String limit = startIdx + ", " + pageSize;

		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel_chapter", new String[]{"n_id", "n_novel", "c_name", "n_order"}, "n_novel = ?", new String[]{novel + ""}, null, null, "n_order asc", limit);
		if(cursor == null)
		{
			return null;
		}

		List<LocalChapter> list = new ArrayList<LocalChapter>();
		cursor.moveToFirst();
		while(! cursor.isAfterLast())
		{
			LocalChapter chapter = new LocalChapter();
			chapter.id = cursor.getInt(0);
			chapter.novel = cursor.getInt(1);
			chapter.name = cursor.getString(2);
			chapter.order = cursor.getInt(3);
			list.add(chapter);

			cursor.moveToNext();
		}

		cursor.close();
		db.close();
		doh.close();
		return list;
	}

	/**
	 * 获取本地章节
	 * @param id
	 */
	public synchronized LocalChapter getLocalChapter(int id)
	{
		LocalChapter chapter = new LocalChapter();
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel_chapter", new String[]{"n_id", "n_novel", "c_name", "n_order"}, "n_id = ?", new String[]{id + ""}, null, null, null, "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		chapter.id = cursor.getInt(0);
		chapter.novel = cursor.getInt(1);
		chapter.name = cursor.getString(2);
		chapter.order = cursor.getInt(3);

		cursor.close();
		db.close();
		doh.close();

		//	读取本地文件
		InputStream is = null;
		try
		{
			//	确定文件保存路径
			String path = AddressConstant.NOVEL_CHAPTER_LOCAL.replace("ID", id + "");
			File file = new File(path);
			if(! file.exists())
			{
				return null;
			}

			is = new FileInputStream(file);
			int len = is.available();
			byte[] buffer = new byte[len];
			is.read(buffer);
			chapter.content = new String(buffer, "UTF-8");
		}
		catch(Exception e)
		{
			Log.d(this.getClass().getName(), "", e);
			return null;
		}
		finally
		{
			if(is != null)
			{
				try{ is.close(); } catch(Exception e) { };
			}
		}

		return chapter;
	}

	/**
	 * 获取下一个本地章节
	 * @param id 当前章节ID
	 */
	public synchronized LocalChapter getNextLocalChapter(int id)
	{
		LocalChapter current = getLocalChapter(id);
		if(current == null)
		{
			return null;
		}

		LocalChapter chapter = new LocalChapter();
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel_chapter", new String[]{"n_id", "n_novel", "c_name", "n_order"}, "n_order > ? and n_novel = ?", new String[]{current.order + "", current.novel + ""}, null, null, "n_order asc", "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		chapter.id = cursor.getInt(0);
		chapter.novel = cursor.getInt(1);
		chapter.name = cursor.getString(2);
		chapter.order = cursor.getInt(3);

		cursor.close();
		db.close();
		doh.close();

		//	读取本地文件
		InputStream is = null;
		try
		{
			//	确定文件保存路径
			String path = AddressConstant.NOVEL_CHAPTER_LOCAL.replace("ID", chapter.id + "");
			File file = new File(path);
			if(! file.exists())
			{
				return null;
			}

			is = new FileInputStream(file);
			int len = is.available();
			byte[] buffer = new byte[len];
			is.read(buffer);
			chapter.content = new String(buffer, "UTF-8");
		}
		catch(Exception e)
		{
			Log.d(this.getClass().getName(), "", e);
			return null;
		}
		finally
		{
			if(is != null)
			{
				try{ is.close(); } catch(Exception e) { };
			}
		}

		return chapter;
	}

	/**
	 * 获取上一个本地章节
	 * @param id 当前章节ID
	 */
	public synchronized LocalChapter getPrevLocalChapter(int id)
	{
		LocalChapter current = getLocalChapter(id);
		if(current == null)
		{
			return null;
		}

		LocalChapter chapter = new LocalChapter();
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getReadableDatabase();
		Cursor cursor = db.query("tbl_novel_chapter", new String[]{"n_id", "n_novel", "c_name", "n_order"}, "n_order < ? and n_novel = ?", new String[]{current.order + "", current.novel + ""}, null, null, "n_order desc", "0, 1");
		if(cursor == null)
		{
			db.close();
			doh.close();
			return null;
		}

		cursor.moveToFirst();
		if(cursor.isAfterLast())
		{
			cursor.close();
			db.close();
			doh.close();
			return null;
		}

		chapter.id = cursor.getInt(0);
		chapter.novel = cursor.getInt(1);
		chapter.name = cursor.getString(2);
		chapter.order = cursor.getInt(3);

		cursor.close();
		db.close();
		doh.close();

		//	读取本地文件
		InputStream is = null;
		try
		{
			//	确定文件保存路径
			String path = AddressConstant.NOVEL_CHAPTER_LOCAL.replace("ID", chapter.id + "");
			File file = new File(path);
			if(! file.exists())
			{
				return null;
			}

			is = new FileInputStream(file);
			int len = is.available();
			byte[] buffer = new byte[len];
			is.read(buffer);
			chapter.content = new String(buffer, "UTF-8");
		}
		catch(Exception e)
		{
			Log.d(this.getClass().getName(), "", e);
			return null;
		}
		finally
		{
			if(is != null)
			{
				try{ is.close(); } catch(Exception e) { };
			}
		}

		return chapter;
	}

	/**
	 * 保存或更新小说章节信息
	 * @param id
	 * @param novel
	 * @param name
	 * @param order
	 * @param content
	 */
	public synchronized void saveOrUpdateChapter(int id, int novel, String name, int order, String content)
	{
		OutputStream os = null;
		try
		{
			//	确定文件保存路径
			String path = AddressConstant.NOVEL_CHAPTER_LOCAL.replace("ID", id + "");
			File file = new File(path);
			File dir = file.getParentFile();
			if(! dir.exists())
			{
				dir.mkdirs();
			}
			if(file.exists())
			{
				//	无论如何，文件存在就删掉
				file.delete();
			}

			//	保存文件
			file.createNewFile();
			os = new FileOutputStream(file);
			byte[] buffer = content.getBytes("UTF-8");
			os.write(buffer);
		}
		catch(Exception e)
		{
			Log.d(this.getClass().getName(), "", e);
		}
		finally
		{
			if(os != null)
			{
				try{ os.close(); } catch(Exception e) { };
			}
		}

		//	写数据库
		LocalChapter chapter = getLocalChapter(id);
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		if(chapter != null)
		{
			//	记录已存在，更新
			ContentValues cv = new ContentValues();
			cv.put("n_novel", novel);
			cv.put("n_order", order);
			cv.put("c_name", name);
			db.update("tbl_novel_chapter", cv, "n_id = ?", new String[]{id + ""});
		}
		else
		{
			//	该记录不存在，插入一条
			ContentValues cv = new ContentValues();
			cv.put("n_id", id);
			cv.put("n_novel", novel);
			cv.put("n_order", order);
			cv.put("c_name", name);
			db.insert("tbl_novel_chapter", null, cv);
		}

		db.close();
		doh.close();
	}

	/**
	 * 删除本地小说
	 * @param novel
	 */
	public void removeLocalNovel(int novel)
	{
		//	删除章节内容
		int total = getLocalChapterPageCount(novel, 1);
		List<LocalChapter> list = getLocalChapterList(novel, total, 1);
		for(LocalChapter lc : list)
		{
			String path = AddressConstant.NOVEL_CHAPTER_LOCAL.replace("ID", lc.id + "");
			File file = new File(path);
			if(file.exists())
			{
				file.delete();
			}
		}

		//	删除上次阅读记录
		DatabaseOpenHelper doh = new DatabaseOpenHelper(context, DatabaseOpenHelper.DATABASE_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
		SQLiteDatabase db = doh.getWritableDatabase();
		db.delete("tbl_last_read", "n_id = ? and n_local = 1", new String[]{novel + ""});

		//	删除章节
		db.delete("tbl_novel_chapter", "n_novel = ?", new String[]{novel + ""});

		//	删除小说
		db.delete("tbl_novel", "n_id = ?", new String[]{novel + ""});

		db.close();
		doh.close();
	}

	/**
	 * 上次阅读位置记录
	 * @author shawn
	 */ 
	public static class LastRead
	{
		public int id;//当前章节ID（与服务器上一致）
		public String category;//类别名称
		public String novel;//当前文章名称
		public boolean local;//是否为本地文件
	}

	/**
	 * 本地小说记录
	 * @author shawn
	 */
	public static class LocalNovel
	{
		public int id;//小说ID（与服务器上一致）
		public String name;//小说名称
	}

	/**
	 * 本地小说章节记录
	 * @author shawn
	 *
	 */
	public class LocalChapter
	{
		public int id;
		public String name;
		public String content;
		public int order;
		public int novel;
	}
}
